Excel BI - Excel Challenge 843

excel-challenges

excel-formulas

🔰 Populate the index where consecutive alphabets appear.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 843

Challenge Description

🔰 Populate the index where consecutive alphabets appear. Consecutive alphabets need to be treated as a single group for the purpose of indexing.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/843/843 Consecutive Index.xlsx"
input = read_excel(path, range = "A2:A23")
test  = read_excel(path, range = "B2:B23")

result = input %>%
  mutate(
    change = input != lag(input, default = first(input)),
    group = cumsum(change)) %>%
  mutate(group = ifelse(n() > 1, group, NA), .by = group) %>%
  mutate(group = dense_rank(group)) %>%
  select(Index = group)

all.equal(result, test)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Apply the business rule conditions explicitly.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd

path = "Excel/800-899/843/843 Consecutive Index.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=22)
test = pd.read_excel(path, usecols="B", skiprows=1, nrows=22)

col = input.iloc[:, 0]
groups = (col != col.shift().fillna(col.iloc[0])).cumsum()
valid = groups.map(groups.value_counts()) > 1
rank_map = {g: i+1 for i, g in enumerate(sorted(groups[valid].unique()))}
result = groups.where(valid).map(rank_map)

print(result.equals(test.Index)) # True

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.